
/****** Object:  View [dbo].[vStayDatesDuration]    Script Date: 04/26/2009 00:39:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vStayDatesDuration]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vStayDatesDuration]
AS
SELECT
DATEPART(MONTH,CheckinDate) as CheckinMonth
,DATEPART(YEAR,CheckinDate) as CheckinYear
,DATEPART(MONTH,CheckoutDate) as CheckoutMonth
,DATEPART(YEAR,CheckoutDate) as CheckoutYear
,DATEDIFF(HOUR,CheckinDate,CheckoutDate) as StayDurationHours
,s.*
FROM Stay s'
GO
/****** Object:  StoredProcedure [dbo].[report_LengthOfStay]    Script Date: 04/26/2009 00:39:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[report_LengthOfStay]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[report_LengthOfStay]
(
	@startDate datetime = null
	,@endDate datetime = null
)
AS
--Length of Stay Report
--By month, for a given date span (one year max)
--For each month, display: Average length of stay
--For each month, display: Shortest length of stay
--For each month, display: Longest length of stay

---- TEST VALUES
--declare @startDate datetime
--declare @endDate datetime
--set @startDate = ''2009-01-01''
--set @endDate = ''2009-01-31''
---- END TEST VALUES

declare @startMonth int
declare @startYear int
set @startMonth = DATEPART(MONTH,@startDate)
set @startYear = DATEPART(YEAR,@startDate)

declare @endMonth int
declare @endYear int
set @endMonth = DATEPART(MONTH,@endDate)
set @endYear = DATEPART(YEAR,@endDate)

SELECT DISTINCT
s.CheckinMonth
,s.CheckinYear
,avgStay.AverageStayHours
,shortStay.ShortestStayHours
,longStay.LongestStayHours
FROM vStayDatesDuration s
LEFT JOIN
(
	-- Average Stay by month
	SELECT
	CheckinMonth
	,AVG(StayDurationHours) as AverageStayHours
	FROM vStayDatesDuration s
	WHERE CheckinMonth = CheckoutMonth
	AND
	(
		(@startDate IS NULL) OR (CheckinDate >= @startDate)
	)
	AND
	(
		(@endDate IS NULL) OR (CheckinDate <= @endDate)
	)	
	GROUP BY CheckinMonth
) avgStay ON s.CheckinMonth = avgStay.CheckinMonth
LEFT JOIN
(
	-- Shortest Stay by month
	SELECT
	CheckinMonth
	,MIN(StayDurationHours) as ShortestStayHours
	FROM vStayDatesDuration s
	WHERE CheckinMonth = CheckoutMonth
	AND
	(
		(@startDate IS NULL) OR (CheckinDate >= @startDate)
	)
	AND
	(
		(@endDate IS NULL) OR (CheckinDate <= @endDate)
	)		
	GROUP BY CheckinMonth
) shortStay ON s.CheckinMonth = shortStay.CheckinMonth
LEFT JOIN
(
	-- Longest Stay by month
	SELECT
	CheckinMonth
	,MAX(StayDurationHours) as LongestStayHours
	FROM vStayDatesDuration s
	WHERE CheckinMonth = CheckoutMonth
	AND
	(
		(@startDate IS NULL) OR (CheckinDate >= @startDate)
	)
	AND
	(
		(@endDate IS NULL) OR (CheckinDate <= @endDate)
	)	
	GROUP BY CheckinMonth
) longStay ON s.CheckinMonth = longStay.CheckinMonth' 
END
GO
